<!DOCTYPE html>
<html lang="en">
	<head>
		<meta charset="UTF-8">
		<title>Excel批量处理</title>
		<script src="https://cdn.bootcss.com/jquery/3.2.1/jquery.js"></script>
		<script src="https://cdn.bootcss.com/xlsx/0.11.5/xlsx.core.min.js"></script>
	</head>
	<body>
		<input type="file" id="excel-file">
		<p>结果</p>
		<pre id="code"></pre>
<!-- 		<textarea id="area" rows="100" cols="100"></textarea>
 -->		<script>
			//给input标签绑定change事件，一上传选中的.xls文件就会触发该函数
			$('#excel-file').change(function(e) {
				var files = e.target.files;
				var fileReader = new FileReader();
				fileReader.onload = function(ev) {
					try {
						var data = ev.target.result
						var workbook = XLSX.read(data, {
							type: 'binary'
						}) // 以二进制流方式读取得到整份excel表格对象
						var persons = []; // 存储获取到的数据
					} catch (e) {
						console.log('文件类型不正确');
						return;
					}
					// 表格的表格范围，可用于判断表头是否数量是否正确
					var fromTo = '';
					var sql = "";
					var i=0;
					// 遍历每张表读取
					for (var sheet in workbook.Sheets) {
						if(i>0){
							if (workbook.Sheets.hasOwnProperty(sheet)) {
								fromTo = workbook.Sheets[sheet]['!ref'];
								let sht = workbook.Sheets[sheet];
								
								window.sht = sht;
								//persons = persons.concat(XLSX.utils.sheet_to_json(workbook.Sheets[sheet]));
								let tableComment = sht.B3.v;//表注释
								let tableName = sht.B1.v;//表名
								//生成表注释sql
								let tableSql = "comment on table REDSUN."+tableName+" is '"+tableComment+"';";
								//生成sql
								let rowSql = buildSql(tableName,sht)
								$("#code").append(tableSql+"<br/>");
							}
						}
						i++;
					}
					//在控制台打印出来表格中的数据
					//$("#area").val(JSON.stringify(persons));
				};
				// 以二进制方式打开文件
				fileReader.readAsBinaryString(files[0]);
			});
			function buildSql(tableName,sheet) {
			    //sht.B1.v;
				let index=5;
				let sqlRes = "";
			    do {
			        let columnName = ""
			          , columnComment = "";
			        //字段
			        let columnNameRc = "B" + index;
			        let columnNameCell = sheet[columnNameRc];
			        if (columnNameCell) {
			            columnName = columnNameCell.v;
			        }else{
			            return;
			        }
			        //字段注释
			        let columnCommentRc = "I" + index;
			        let columnCommentCell = sheet[columnCommentRc];
			        if (columnCommentCell) {
			            columnComment = columnCommentCell.v;
			        }
			        let sql = "comment on column REDSUN.ODS_SAP_"+tableName+"."+columnName+" is '"+columnComment+"';";
			        $("#code").append(sql+"<br/>");
					index++;
			    } while (true);
				return sqlRes;
			}

		</script>
	</body>
</html>